
import requests
import time
import re
from mysql_handler import MysqlHandler
import  datetime
import json



import pymysql.cursors


mysql_handler = MysqlHandler()

mycursor = pymysql.connect(host="localhost", user="root", passwd="root", db="codespider", charset="utf8",
						  cursorclass=pymysql.cursors.DictCursor)


# 获取省与直辖市
def get_Province_list():
	# 返回一个 response 对象
	response = session.get('https://www.stats.gov.cn/sj/tjbz/tjyqhdmhcxhfdm/2023/index.html')  # 单位秒数
	content = response.html.find('table.provincetable', first=True)
	li_list = content.find('a')
	for li in li_list:
		url = li.attrs['href']
		code = re.findall("\d+",url)[0]
		area_code = str(code)+'0000000000'
		area_name=li.text
		# SQL 插入语句
		sql = "INSERT INTO area_code (area_name, area_code,parent_code,parent_id) VALUES (%s, %s, %s, %s)"
		val = (area_name, area_code, 0 ,0)
		mycursor.execute(sql, val)
		pid = mycursor.lastrowid
		mydb.commit()  # 数据表内容有更新，必须使用到该语句
		get_City_list(code,area_code, pid)

#获取市
def get_City_list(code,parent_code, pid):
	# 返回一个 response 对象
	response = session.get('https://www.stats.gov.cn/sj/tjbz/tjyqhdmhcxhfdm/2023/'+code+'.html')  # 单位秒数
	content = response.html.find('table.citytable', first=True)
	citys = content.find('tr.citytr')
	for city in citys:
		td_list = city.find('td')
		ycode = td_list[0].find('a')[0]
		code1 = ycode.text[0:4]
		area_name = td_list[1].find('a')[0].text
		area_code = ycode.text[0:12]
		# SQL 插入语句
		sql = "INSERT INTO area_code (area_name, area_code,parent_code,parent_id) VALUES (%s, %s, %s, %s)"
		val = (area_name, area_code, parent_code, pid)
		mycursor.execute(sql, val)
		pid1 = mycursor.lastrowid
		mydb.commit()  # 数据表内容有更新，必须使用到该语句
		get_County_list(code,code1,area_code,pid1)
#获取县级市
def get_County_list(code,code1,parent_code,pid1):
	# 返回一个 response 对象
	response = session.get('https://www.stats.gov.cn/sj/tjbz/tjyqhdmhcxhfdm/2023/'+code+'/'+code1+'.html')
	content = response.html.find('table.countytable', first=True)
	if content:
		citys = content.find('tr.countytr')
		for city in citys:
			td_list = city.find('td')
			ycode=td_list[0].find('a')
			if len(ycode) == 0:
				area_code =td_list[0].text
				area_name = td_list[1].text
			else:
				area_code = ycode[0].text[0:12]
				area_name = td_list[1].find('a')[0].text
				code2 = area_code[2:4]
				code3 = ycode[0].text[0:6]
				sql = "INSERT INTO area_code (area_name, area_code,parent_code,parent_id) VALUES (%s,%s, %s, %s)"
				val = (area_name, area_code, code, pid1)
				mycursor.execute(sql, val)
				pid2 = mycursor.lastrowid
				mydb.commit()  # 数据表内容有更新，必须使用到该语句
				get_jd_data(code,code2,code3,area_code,pid2)

def get_jd_data(code,code2,code3,parent_code,pid2):
	response = session.get('https://www.stats.gov.cn/sj/tjbz/tjyqhdmhcxhfdm/2023/' + code + '/' + code2 + '/' + code3 + '.html')
	content = response.html.find('table.towntable', first=True)
	if content:
		citys = content.find('tr.towntr')
		for city in citys:
			td_list = city.find('td')
			ycode = td_list[0].find('a')
			if len(ycode) == 0:
				area_code = td_list[0].text
				area_name = td_list[1].text
			else:
				area_code = ycode[0].text[0:12]
				area_name = td_list[1].find('a')[0].text
				sql = "INSERT INTO area_code (area_name, area_code,parent_code,parent_id) VALUES (%s,%s, %s, %s)"
				val = (area_name, area_code, parent_code, pid2)
				mycursor.execute(sql, val)
				mydb.commit()  # 数据表内容有更新，必须使用到该语句

if __name__ == '__main__':
	get_Province_list()

